const XLSX = require('xlsx');
const fs = require('fs');
const path = require('path');

class NdPlots {
  constructor() {
    this.id = null;
    this.plotId = null;
    this.originalPlotId = null;
    this.ecosystemTypeVegetation = null;
    this.vegetationGroup = null;
    this.communityName = null;
    this.location = null;
    this.longitude = null;
    this.latitude = null;
    this.elevation = null;
    this.topography = null;
    this.slopeDirection = null;
    this.slopePosition = null;
    this.slopeDegree = null;
    this.disturbanceLevel = null;
    this.disturbanceType = null;
    this.artificialPlanting = null;
    this.treeLayer = null;
    this.shrubLayer = null;
    this.herbLayer = null;
    this.mossLichenLayer = null;
    this.surfaceFeatures = null;
    this.soilType = null;
    this.soilTexture = null;
    this.soilMeasurement = null;
    this.investigator = null;
    this.recorder = null;
    this.surveyDate = null;
    this.otherNotes = null;
    this.ecosystemCategory = null;
    this.projectLeader = null;
  }
}

class NdTrees {
  constructor() {
    this.id = null;
    this.plotId = null;
    this.newPlotId = null;
    this.originalPlotId = null;
    this.treeQuadratId = null;
    this.quadratRecorder = null;
    this.quadratArea = null;
    this.totalCoveragePct = null;
    this.layerHeightCm = null;
    this.measurementStandard = null;
    this.speciesField = null;
    this.chineseName = null;
    this.latinName = null;
    this.plantId = null;
    this.heightM = null;
    this.branchHeightM = null;
    this.dbhCm = null;
    this.circumferenceCm = null;
    this.crownLongAxisCm = null;
    this.crownShortAxisCm = null;
    this.biomassGM2 = null;
    this.biomassMethod = null;
    this.photoId = null;
    this.surveyYear = null;
    this.ecosystemType = null;
    this.projectLeader = null;
  }
}

class NdShrubs {
  constructor() {
    this.id = null;
    this.plotId = null;
    this.newPlotId = null;
    this.originalPlotId = null;
    this.shrubQuadratId = null;
    this.quadratRecorder = null;
    this.quadratArea = null;
    this.totalCoveragePct = null;
    this.layerHeightCm = null;
    this.speciesField = null;
    this.chineseName = null;
    this.latinName = null;
    this.plantId = null;
    this.averageHeightCm = null;
    this.stemCount = null;
    this.averageStemBaseDiameterCm = null;
    this.crownLongAxisCm = null;
    this.crownShortAxisCm = null;
    this.biomassGM2 = null;
    this.biomassMethod = null;
    this.photoId = null;
    this.clusterCount = null;
    this.surveyYear = null;
    this.ecosystemType = null;
    this.projectLeader = null;
  }
}

class NdHerbs {
  constructor() {
    this.id = null;
    this.plotId = null;
    this.originalPlotId = null;
    this.herbQuadratId = null;
    this.quadratRecorder = null;
    this.quadratAreaM2 = null;
    this.totalCoveragePct = null;
    this.layerHeightCm = null;
    this.speciesField = null;
    this.chineseName = null;
    this.latinNameField = null;
    this.reproductiveBranchHeightCm = null;
    this.vegetativeBranchHeightCm = null;
    this.partialCoveragePct = null;
    this.clusterCount = null;
    this.biomassFreshGM2 = null;
    this.biomassDryGM2 = null;
    this.abundanceLevel = null;
    this.notes = null;
    this.photoId = null;
    this.surveyYear = null;
    this.ecosystemType = null;
    this.subProjectLeader = null;
  }
}

// 动物调查---
class dwdcData {
  constructor() {
    this.wzm = null;
    this.ldm = null;
    this.ldmfull = null;
    this.shitishuliang = null;
    this.henjishuliang = null;
    this.jd = null;
    this.wd = null;
    this.hb = null;
    this.podu = null;
    this.powei = null;
    this.poxiang = null;
    this.qixi = null;
    this.diaochariqi = null;
    this.qixiganrao = null;
    this.protectlevel = null;
    this.location = null;
    this.mengshi = null;
    this.qixian = null;
    this.source = null;
    this.guance = null;
    this.zpbh = null;
    this.sjzpbh = null;
    this.tezhengmiaoshu = null;
    this.fenbuduodumiaoshu = null;
    this.category1 = null;
  }
}

// 动物调查---
class wzfbData {
  constructor() {
    this.xzqhcode = '';
    this.chineseName = '';
    this.latinName = '';
    this.county = '';
    this.leagueCity = '';
  }
}

// const FILE_PATH = "./template.xlsx";

export function readExcel(workbook) {
  // const workbook = XLSX.readFile(FILE_PATH);

  const plots = readPlots(workbook.Sheets['样地信息']);
  const trees = readTrees(workbook.Sheets['乔木样方']);
  const shrubs = readShrubs(workbook.Sheets['灌木样方']);
  const herbs = readHerbs(workbook.Sheets['草本样方']);

  // For demonstration, return or log the data
  return { plots, trees, shrubs, herbs };
}

export function readExcelDwdc(workbook) {
  const dwdc = readDwdc(workbook.Sheets[workbook.SheetNames[0]]);
  return { dwdc }
}

export function readExcelWzfb(workbook) {
  const wzfb = readWzfb(workbook.Sheets[workbook.SheetNames[0]]);
  return { wzfb }
}

function readPlots(sheet) {
  const plots = [];
  if (!sheet) return plots;

  const range = XLSX.utils.decode_range(sheet['!ref']);
  for (let rowNum = range.s.r + 1; rowNum <= range.e.r; rowNum++) { // Start from row 1 (0-based index, skip header)
    const row = getRow(sheet, rowNum);
    if (!row) continue;

    const plot = new NdPlots();
    // Ignore column 0: order
    plot.plotId = getString(sheet, rowNum, 1);
    const originalPlotid = getString(sheet, rowNum, 2);
    if (originalPlotid === null) continue;
    plot.originalPlotId = originalPlotid;
    plot.ecosystemTypeVegetation = getString(sheet, rowNum, 3);
    plot.vegetationGroup = getString(sheet, rowNum, 4);
    plot.communityName = getString(sheet, rowNum, 5);
    plot.location = getString(sheet, rowNum, 6);
    plot.longitude = getBigDecimal(sheet, rowNum, 7);
    plot.latitude = getBigDecimal(sheet, rowNum, 8);
    plot.elevation = getBigDecimal(sheet, rowNum, 9);
    plot.topography = getString(sheet, rowNum, 10);
    plot.slopeDirection = getString(sheet, rowNum, 11);
    plot.slopePosition = getString(sheet, rowNum, 12);
    plot.slopeDegree = getInteger(sheet, rowNum, 13);
    plot.disturbanceLevel = getString(sheet, rowNum, 14);
    plot.disturbanceType = getString(sheet, rowNum, 15);
    plot.artificialPlanting = getString(sheet, rowNum, 16);
    plot.treeLayer = getString(sheet, rowNum, 17);
    plot.shrubLayer = getString(sheet, rowNum, 18);
    plot.herbLayer = getString(sheet, rowNum, 19);
    plot.mossLichenLayer = getString(sheet, rowNum, 20);
    plot.surfaceFeatures = getString(sheet, rowNum, 21);
    plot.soilType = getString(sheet, rowNum, 22);
    plot.soilTexture = getString(sheet, rowNum, 23);
    plot.soilMeasurement = getString(sheet, rowNum, 24);
    plot.investigator = getString(sheet, rowNum, 25);
    plot.recorder = getString(sheet, rowNum, 26);
    plot.surveyDate = getDate(sheet, rowNum, 27);
    plot.otherNotes = getString(sheet, rowNum, 28);
    plot.ecosystemCategory = getString(sheet, rowNum, 29);
    plot.projectLeader = getString(sheet, rowNum, 30);

    plots.push(plot);
  }
  return plots;
}

function readTrees(sheet) {
  const trees = [];
  if (!sheet) return trees;

  const range = XLSX.utils.decode_range(sheet['!ref']);
  for (let rowNum = range.s.r + 1; rowNum <= range.e.r; rowNum++) {
    const row = getRow(sheet, rowNum);
    if (!row) continue;

    const tree = new NdTrees();
    tree.plotId = getString(sheet, rowNum, 0);
    tree.newPlotId = getString(sheet, rowNum, 1);
    const originalPlotid = getString(sheet, rowNum, 2);
    if (originalPlotid === null) continue;
    tree.originalPlotId = originalPlotid;
    tree.treeQuadratId = getString(sheet, rowNum, 3);
    tree.quadratRecorder = getString(sheet, rowNum, 4);
    tree.quadratArea = getString(sheet, rowNum, 5);
    tree.totalCoveragePct = getBigDecimal(sheet, rowNum, 6);
    tree.layerHeightCm = getBigDecimal(sheet, rowNum, 7);
    tree.measurementStandard = getString(sheet, rowNum, 8);
    tree.speciesField = getString(sheet, rowNum, 9);
    tree.chineseName = getString(sheet, rowNum, 10);
    tree.latinName = getString(sheet, rowNum, 11);
    tree.plantId = getString(sheet, rowNum, 12);
    tree.heightM = getBigDecimal(sheet, rowNum, 13);
    tree.branchHeightM = getBigDecimal(sheet, rowNum, 14);
    tree.dbhCm = getBigDecimal(sheet, rowNum, 15);
    tree.circumferenceCm = getBigDecimal(sheet, rowNum, 16);
    tree.crownLongAxisCm = getBigDecimal(sheet, rowNum, 17);
    tree.crownShortAxisCm = getBigDecimal(sheet, rowNum, 18);
    tree.biomassGM2 = getBigDecimal(sheet, rowNum, 19);
    tree.biomassMethod = getString(sheet, rowNum, 20);
    tree.photoId = getString(sheet, rowNum, 21);
    tree.surveyYear = getInteger(sheet, rowNum, 22);
    tree.ecosystemType = getString(sheet, rowNum, 23);
    tree.projectLeader = getString(sheet, rowNum, 24);

    trees.push(tree);
  }
  return trees;
}

function readShrubs(sheet) {
  const shrubs = [];
  if (!sheet) return shrubs;

  const range = XLSX.utils.decode_range(sheet['!ref']);
  for (let rowNum = range.s.r + 1; rowNum <= range.e.r; rowNum++) {
    const row = getRow(sheet, rowNum);
    if (!row) continue;

    const shrub = new NdShrubs();
    shrub.plotId = getString(sheet, rowNum, 0);
    shrub.newPlotId = getString(sheet, rowNum, 1);
    const originalPlotid = getString(sheet, rowNum, 2);
    if (originalPlotid === null) continue;
    shrub.originalPlotId = originalPlotid;
    shrub.shrubQuadratId = getString(sheet, rowNum, 3);
    shrub.quadratRecorder = getString(sheet, rowNum, 4);
    shrub.quadratArea = getString(sheet, rowNum, 5);
    shrub.totalCoveragePct = getBigDecimal(sheet, rowNum, 6);
    shrub.layerHeightCm = getBigDecimal(sheet, rowNum, 7);
    shrub.speciesField = getString(sheet, rowNum, 8);
    shrub.chineseName = getString(sheet, rowNum, 9);
    shrub.latinName = getString(sheet, rowNum, 10);
    shrub.plantId = getString(sheet, rowNum, 11);
    shrub.averageHeightCm = getBigDecimal(sheet, rowNum, 12);
    shrub.stemCount = getInteger(sheet, rowNum, 13);
    shrub.averageStemBaseDiameterCm = getBigDecimal(sheet, rowNum, 14);
    shrub.crownLongAxisCm = getBigDecimal(sheet, rowNum, 15);
    shrub.crownShortAxisCm = getBigDecimal(sheet, rowNum, 16);
    shrub.biomassGM2 = getBigDecimal(sheet, rowNum, 17);
    shrub.biomassMethod = getString(sheet, rowNum, 18);
    shrub.photoId = getString(sheet, rowNum, 19);
    shrub.clusterCount = getInteger(sheet, rowNum, 20);
    shrub.surveyYear = getInteger(sheet, rowNum, 21);
    shrub.ecosystemType = getString(sheet, rowNum, 22);
    shrub.projectLeader = getString(sheet, rowNum, 23);

    shrubs.push(shrub);
  }
  return shrubs;
}

function readHerbs(sheet) {
  const herbs = [];
  if (!sheet) return herbs;

  const range = XLSX.utils.decode_range(sheet['!ref']);
  for (let rowNum = range.s.r + 1; rowNum <= range.e.r; rowNum++) {
    const row = getRow(sheet, rowNum);
    if (!row) continue;

    const herb = new NdHerbs();
    // Ignore column 0: CODE
    herb.plotId = getString(sheet, rowNum, 1);
    const originalPlotid = getString(sheet, rowNum, 2);
    if (originalPlotid === null) continue;
    herb.originalPlotId = originalPlotid;
    herb.herbQuadratId = getString(sheet, rowNum, 3);
    herb.quadratRecorder = getString(sheet, rowNum, 4);
    herb.quadratAreaM2 = getString(sheet, rowNum, 5);
    herb.totalCoveragePct = getBigDecimal(sheet, rowNum, 6);
    herb.layerHeightCm = getBigDecimal(sheet, rowNum, 7);
    herb.speciesField = getString(sheet, rowNum, 8);
    herb.chineseName = getString(sheet, rowNum, 9);
    herb.latinNameField = getString(sheet, rowNum, 10);
    herb.reproductiveBranchHeightCm = getBigDecimal(sheet, rowNum, 11);
    herb.vegetativeBranchHeightCm = getBigDecimal(sheet, rowNum, 12);
    herb.partialCoveragePct = getBigDecimal(sheet, rowNum, 13);
    herb.clusterCount = getInteger(sheet, rowNum, 14);
    herb.biomassFreshGM2 = getBigDecimal(sheet, rowNum, 15);
    herb.biomassDryGM2 = getBigDecimal(sheet, rowNum, 16);
    herb.abundanceLevel = getString(sheet, rowNum, 17);
    herb.notes = getString(sheet, rowNum, 18);
    herb.photoId = getString(sheet, rowNum, 19);
    herb.surveyYear = getInteger(sheet, rowNum, 20);
    herb.ecosystemType = getString(sheet, rowNum, 21);
    herb.subProjectLeader = getString(sheet, rowNum, 22);

    herbs.push(herb);
  }
  return herbs;
}

// 动物调查--------
function readDwdc(sheet) {
  const dwdcs = [];
  if (!sheet) return dwdcs;

  const range = XLSX.utils.decode_range(sheet['!ref']);
  for (let rowNum = range.s.r + 1; rowNum <= range.e.r; rowNum++) {
    const row = getRow(sheet, rowNum);
    if (!row) continue;

    const dwdc = new dwdcData();
    // Ignore column 0: CODE
    // if (category1 === null) continue;
    dwdc.wzm = getString(sheet, rowNum, 1);
    dwdc.ldm = getString(sheet, rowNum, 2);
    dwdc.ldmfull = getString(sheet, rowNum, 3);
    dwdc.shitishuliang = getBigDecimal(sheet, rowNum, 4);
    dwdc.henjishuliang = getBigDecimal(sheet, rowNum, 5);
    dwdc.jd = getBigDecimal(sheet, rowNum, 6);
    dwdc.wd = getBigDecimal(sheet, rowNum, 7);
    dwdc.hb = getBigDecimal(sheet, rowNum, 8);
    dwdc.podu = getBigDecimal(sheet, rowNum, 9);
    dwdc.powei = getString(sheet, rowNum, 10);
    dwdc.poxiang = getString(sheet, rowNum, 11);
    dwdc.qixi = getString(sheet, rowNum, 12);
    dwdc.diaochariqi = getDate(sheet, rowNum, 13);
    dwdc.qixiganrao = getString(sheet, rowNum, 14);
    dwdc.protectlevel = getString(sheet, rowNum, 15);
    dwdc.location = getString(sheet, rowNum, 16);
    dwdc.mengshi = getString(sheet, rowNum, 17);
    dwdc.qixian = getString(sheet, rowNum, 18);
    dwdc.source = getString(sheet, rowNum, 19);
    dwdc.guance = getString(sheet, rowNum, 20);
    dwdc.zpbh = getString(sheet, rowNum, 21);
    dwdc.sjzpbh = getString(sheet, rowNum, 22);
    dwdc.tezhengmiaoshu = getString(sheet, rowNum, 23);
    dwdc.fenbuduodumiaoshu = getString(sheet, rowNum, 24);
    dwdc.category1 = getString(sheet, rowNum, 25);

    dwdcs.push(dwdc);
  }
  return dwdcs;
}

// 物种分布--------
function readWzfb(sheet) {
  const wzfbs = [];
  if (!sheet) return wzfbs;

  const range = XLSX.utils.decode_range(sheet['!ref']);
  for (let rowNum = range.s.r + 1; rowNum <= range.e.r; rowNum++) {
    const row = getRow(sheet, rowNum);
    if (!row) continue;

    const wzfb = new wzfbData();
    // Ignore column 0: CODE

    const xzqhcode = getString(sheet, rowNum, 1);
    if (xzqhcode === null) continue;
    wzfb.xzqhcode = xzqhcode;
    wzfb.chineseName = getString(sheet, rowNum, 2);
    wzfb.latinName = getString(sheet, rowNum, 3);
    wzfb.county = getString(sheet, rowNum, 4);
    wzfb.leagueCity = getString(sheet, rowNum, 5);
    // wzfb.category = 'bird';

    wzfbs.push(wzfb);
  }
  return wzfbs;
}

function getRow(sheet, rowNum) {
  // In xlsx, rows are not directly accessible; we use cell addresses
  return true; // Placeholder, since we check cells individually
}

function getString(sheet, rowNum, col) {
  const cellAddress = XLSX.utils.encode_cell({ r: rowNum, c: col });
  const cell = sheet[cellAddress];
  if (!cell) return null;
  const val = (cell.v || '').toString().trim();
  return val === '' ? null : val;
}

function getBigDecimal(sheet, rowNum, col) {
  const val = getString(sheet, rowNum, col);
  if (val === null) return null;
  const num = parseFloat(val);
  return isNaN(num) ? null : num;
}

function getInteger(sheet, rowNum, col) {
  const val = getString(sheet, rowNum, col);
  if (val === null) return null;
  const num = parseInt(val, 10);
  return isNaN(num) ? null : num;
}

function getDate(sheet, rowNum, col) {
  const val = getString(sheet, rowNum, col);
  if (val === null || val.trim() === '') return null;

  const parsedDate = parseDate(val);
  if (parsedDate !== null) {
    return new Date(parsedDate);
  } else {
    return null;
  }
}

function parseDate(dateStr) {
  try {
    let year = 0;
    let month = 1; // Default to January if only year, but no such case
    let day = 1;   // Default day to 1 if not provided

    if (dateStr.includes('.')) {
      // Format like 2021.8 or 2022.7.5 or 2023.8.29
      const parts = dateStr.split('.');
      if (parts.length < 2 || parts.length > 3) return null;

      year = parseInt(parts[0].trim(), 10);
      month = parseInt(parts[1].trim(), 10);
      if (parts.length === 3) {
        day = parseInt(parts[2].trim(), 10);
      }
    } else {
      // Format like 20210701 or 20230731
      if (dateStr.length === 8 && /^\d{8}$/.test(dateStr)) {
        year = parseInt(dateStr.substring(0, 4), 10);
        month = parseInt(dateStr.substring(4, 6), 10);
        day = parseInt(dateStr.substring(6, 8), 10);
      } else {
        return null;
      }
    }

    if (day > 31) {
      day = 31;
    }

    // Validate date
    const date = new Date(year, month - 1, day);
    if (isNaN(date.getTime())) return null;
    return date.toISOString().split('T')[0]; // Return YYYY-MM-DD string
  } catch (e) {
    console.error("Invalid date: " + dateStr);
    return null;
  }
}

// Example usage
// const data = readExcel();
// console.log(`Read ${data.plots.length} plots.`);
// console.log(`Read ${data.trees.length} trees.`);
// console.log(`Read ${data.shrubs.length} shrubs.`);
// console.log(`Read ${data.herbs.length} herbs.`);